from datetime import datetime
import time
import requests
import pandas as pd
from flask import Flask, render_template_string, send_file, make_response
from datetime import datetime
import os
import pandas as pd

app = Flask(__name__)

def get_city(df, lm):
    get_province(df, lm)
    df_shi = pd.read_excel('地区划码（省市区街道）.xlsx', sheet_name='市')
    df_shi['简化市'] = df_shi['市'].str.replace('市', '').str.replace('自治州', '').str.replace('地区', '')
    df_shi['简化省'] = df_shi['省'].str.replace('省', '').str.replace('自治区', '').str.replace('市', '')
    city_index = df_shi.set_index('简化市')['市'].to_dict()
    df_district = pd.read_excel('地区划码（省市区街道）.xlsx', sheet_name='区县')
    df_district['简化区'] = df_district['区'].str.replace('产业开发区', '').str.replace('自治县', '').str.replace('经济开发区', '')
    df_district['简化市'] = df_district['市'].str.replace('市', '').str.replace('自治州', '').str.replace('地区', '')
    df_district['简化省'] = df_district['省'].str.replace('省', '').str.replace('自治区', '').str.replace('市', '')
    def extract_city_name(dz):
        for city in city_index.keys():
            if city in dz:
                return city_index[city]
        return None
    def extract_district_name(dz):
        fl = df_district[df_district['简化区'].apply(lambda x: x in dz)]
        if len(fl) > 0:
            if len(fl) == 1:
                return fl.iloc[0]['市']
            else:
                temp = df_district[df_district['简化省'].apply(lambda x: x in dz)]
                merged_df = pd.merge(fl, temp, how='inner')
                if len(merged_df) > 0:
                    return merged_df.iloc[0]['市']
                else:
                    return None
        else:
            return None


        # return extract_district_name(dz)
    df["所属城市"] = df[lm].apply(extract_city_name)
    mask = (df["所属城市"].isna())
    filtered_df = df[mask].copy()
    filtered_df["所属城市"] = filtered_df[lm].apply(extract_district_name).copy()
    df.update(filtered_df[["所属城市"]])
    filtered_df = df[(df['所属省份'].isnull()) & (df['所属城市'].notnull())].copy()
    province_map = df_shi.set_index('市')['省'].to_dict()
    filtered_df['所属省份'] = filtered_df['所属城市'].map(province_map).copy()
    df.update(filtered_df[["所属省份"]])
    return df


def get_province(df, lm):
    df_province = pd.read_excel('地区划码（省市区街道）.xlsx', sheet_name='省')
    df_province['简化省'] = df_province['省'].str.replace('省', '').str.replace('自治区', '').str.replace('市', '')
    province_index = df_province.set_index('简化省')['省'].to_dict()
    def extract_province_name(dz):
        for city in province_index.keys():
            if city in dz:
                return province_index[city]
        return None
    df["所属省份"] = df[lm].apply(extract_province_name)
    return df


def get_district(df, lm):
    df_district = pd.read_excel('地区划码（省市区街道）.xlsx', sheet_name='区县')
    df_district['简化区'] = df_district['区'].str.replace('产业开发区', '').str.replace('自治县', '').str.replace('经济开发区','')
    df_district['简化市'] = df_district['市'].str.replace('市', '').str.replace('自治州', '').str.replace('地区', '')
    df_district['简化省'] = df_district['省'].str.replace('省', '').str.replace('自治区', '').str.replace('市', '')
    def extract_district_name(dz):
        fl = df_district[df_district['简化区'].apply(lambda x: x in dz)]
        if len(fl) > 0:
            if len(fl) == 1:
                return fl.iloc[0]['区']
            else:
                return 'xxxx'
                # temp = df_district[df_district['简化省'].apply(lambda x: x in dz)]
                # merged_df = pd.merge(fl, temp, how='inner')
                # merged_df = pd.merge(fl, temp, how='inner')
                # if len(merged_df) > 0:
                #     return merged_df.iloc[0]['区']
                # else:
                #     return None
        else:
            return None
    def district2(dz):
        fl = df_district[df_district['简化区'].apply(lambda x: x in dz)]
        temp = df_district[df_district['简化省'].apply(lambda x: x in dz)]
        merged_df = pd.merge(fl, temp, how='inner')
        if len(merged_df) > 0:
            return merged_df.iloc[0]['区']
        else:
            return None

    df["所属区县"] = df[lm].apply(extract_district_name).copy()
    filtered_df = df[df['所属区县'] == 'xxxx'].copy()
    filtered_df["所属区县"] = filtered_df[lm].apply(district2).copy()
    df.update(filtered_df[["所属区县"]])
    df = get_city(df, lm)
    return df


def get_swb():
    header = {
        'b927e11f4b9f56f588a4': 'fda8c737e62ada444a62881f564e3fe2d72d7f8cad69ff99acf885c3a86abb2a4033166f13939d8c190ce852641ba7729ad13af95cee438b9a8b81a6dff268df',
        'Cookie': '_uab_collina=170140052454617358568261; qcc_did=d33df945-c698-4010-bc4c-188342933187; UM_distinctid=190573d2ab75f1-036d8e0454d5ef-3d267449-1fa400-190573d2ab8e1a; QCCSESSID=46eb97a1fe4510b5c5e25d5fd8; tfstk=fr6st90QQV01TxsBoCEeOKMlilJjfNwrlmtAqiHZDdptkqIJ8hWaSiLflw8n71PcuZgf7w7XilY2kE_VD1rUU8SGjKvvhzyzUuzJzf7sXnhTDXKDXtfESAsGjKmHfaIloGcb-hzlBKQvppKDqKKvXfEBpnx9HAdxBX3p-npvkZLAvBKXqAKxBndKN-iWc5TGfkhvNx2xecS6RxHSqFdtGGlqEYXefC6AXes1f9t61UdIXZOCBwSAQOA39X9cY__fMZUsQHfP7OAF12UXy17GIdX4zjJh6NWBsM4SIF1dSZpNeulDxMsAaB6U28v6BFC2Iio77M617GXC0mhpCd5BNO98EVLVTg6Xct40dasRDTvN3ocpS9tA4nDyPBASGhbqhHTzAkGmgGTwTHKmnE6p6Hx6TkZIWsA9xHTzAkGmiCKHf3rQAVCc.; acw_tc=2f624a7517235326025882945e56d872a011a1ca3702b68a0cb480b5b48c2b; CNZZDATA1254842228=820105048-1687936239-https%253A%252F%252Fwww.baidu.com%252F%7C1723532606',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.5359.125 Safari/537.36'
    }
    branch = []
    company = requests.post(url=f'https://zxgl.mofcom.gov.cn/front/corpJSON', data={"page": 1, "rows": 1000}).json()[
        'rows']
    for each in company:
        time.sleep(1)
        print(each['enterpriseName'], each['licenseNumber'])
        branch_temp = requests.post(
            url=f'https://zxgl.mofcom.gov.cn/front/corpBranchJSON?filter_EQS_enterpriseLicense={each["licenseNumber"]}',
            headers=header,
            data={"page": 1, "rows": 10000}).json()['rows']
        for d in branch_temp:
            d["licenseNumber"] = each['licenseNumber']
            d["enterpriseName"] = each['enterpriseName']
        branch += branch_temp
    product = requests.post(url=f'https://zxgl.mofcom.gov.cn/front/corpProductJSON', headers=header,
                            data={"page": 1, "rows": 100000}).json()['rows']
    service = requests.post(url=f'https://zxgl.mofcom.gov.cn/front/networkJSON', headers=header,
                            data={"page": 1, "rows": 100000}).json()['rows']
    df_company = pd.DataFrame(company)
    df_company =get_city(df_company,'address')
    df_branch = pd.DataFrame(branch)
    df_branch = get_city(df_branch, 'address')
    df_product = pd.DataFrame(product)
    df_service = pd.DataFrame(service)
    df_service = get_city(df_service, 'address')
    current_date = datetime.now().strftime("%Y年%m月%d日")
    # 创建ExcelWriter对象
    writer = pd.ExcelWriter(f'商务部直销企业数据.xlsx')
    # 将数据写入不同的工作表
    df_company.to_excel(writer, sheet_name='直销企业', index=False)
    df_branch.to_excel(writer, sheet_name='分支机构', index=False)
    df_product.to_excel(writer, sheet_name='直销产品', index=False)
    df_service.to_excel(writer, sheet_name='服务网点', index=False)
    writer.close()


# HTML 模板，包含一个下载按钮
HTML_TEMPLATE = '''  
<!DOCTYPE html>  
<html>  
<head>  
    <title>下载直销企业商务部数据</title>  
</head>  
<body>  
    <h1>下载直销企业商务部数据</h1>  
    <form action="/download" method="post">  
        <button type="submit">下载数据</button>  
    </form>  
</body>  
</html>  
'''


@app.route('/swb')
def index():
    return render_template_string(HTML_TEMPLATE)


@app.route('/download', methods=['POST'])
def download():
    # 执行 swb 方法
    get_swb()

    # 获取当前日期，用于文件名
    now = datetime.now().strftime('%Y年%m月%d日')
    file_name = f'商务部直销企业数据{now}.xlsx'

    # 文件路径
    file_path = '商务部直销企业数据.xlsx'  # 假设这个文件已存在

    return send_file(os.path.join('.', '商务部直销企业数据.xlsx') , as_attachment=True, download_name=file_name)


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5003)
